<?php

namespace App\Http\Controllers\Admin\ExportReport;

use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use DB;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use Illuminate\Support\Facades\Storage;

class ExportReportSiChuanTongZhouController extends Controller
{

    /* 导出Excel 检测报告*/
    public function export(Request $request){
        $contract_id = $request->id; //批次 id
        $data_batch = DB::table('a_contract')->find($contract_id);
        $batchNum = $data_batch->batchNum;

        $storeFile = 'reports/'.$batchNum.'.xls';
        $disk = Storage::disk('public');
        if($disk->exists($storeFile)){
            if($data_batch->status > 13) {
                return $disk->download($storeFile);
            }else{
                $disk->delete($storeFile);
            }
        }
        //================================================== 封面 start ============================================
            $template_file = './storage/ExcelTemplate/封面.xls'; //考虑到兼容Linux，斜杠用“/”
            if (!file_exists($template_file)) {
                return '封面模板文件不存在';
            }

            //$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
            $setting1 = DB::table('setting')->find(1);
            $report_num_pre = $setting1->report_num_pre; //报告编号前缀
            $company_name = $setting1->name_rep; //获取检测公司名称
            $headerContent = "&L{$company_name}     &R{$report_num_pre}{$data_batch->batchNum}"; //眉头内容
            $headerPageMargins = 0.5; //眉头的页边距

            $footerContent = '&R&12第 &P 页，共 &N 页'; //页脚内容
            $footerPageMargins = 0.3; //页脚的页边距

            $bottomPageMargins = 0.5; //下边的页边距
            //$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

            $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($template_file); //读取模板文件
            $worksheet = $spreadsheet->getSheet(0); //按索引获取表单，从 0 开始
            $worksheet->setTitle('封面'); //重命名工作表

            $worksheet->getHeaderFooter()->setOddFooter($footerContent); //页脚  Odd
            $worksheet->getHeaderFooter()->setEvenFooter($footerContent); //页脚  Even
            $worksheet->getPageMargins()->setFooter($footerPageMargins); //设置页脚的页边距

            $worksheet->getPageMargins()->setBottom($bottomPageMargins); //设置下边的页边距


            $worksheet->getCell('P18')->setValue($report_num_pre . $data_batch->batchNum); //报告编号前缀 + 报告编号
            //$worksheet->getCell('P27')->setValue($data_batch->client_name); //送检单位
            $worksheet->getCell('P19')->setValue($data_batch->entrusted_company); //委托单位
            $worksheet->getCell('P20')->setValue($data_batch->client_addr); //地址
            $worksheet->getCell('P22')->setValue($data_batch->test_kind); //检测性质
            // $worksheet->getCell('P27')->setValue($data_batch->client_name); //受检单位
        //==================================================== 封面 end ===================================================
        //================================================== 封底 start ============================================
            $template_file = './storage/ExcelTemplate/封底.xls'; //考虑到兼容Linux，斜杠用“/”
            if (file_exists($template_file)) {
                $spreadsheet_common = \PhpOffice\PhpSpreadsheet\IOFactory::load($template_file); //读取模板文件
                $worksheet_common = $spreadsheet_common->getSheet(0); //按索引获取表单，从 0 开始
                $worksheet_common->setTitle('封底'); //重命名工作表

                $worksheet_common->getHeaderFooter()->setOddHeader($headerContent); //检测公司名称 + 报告编号前缀 + 报告编号(封底页眉头)  Odd
                $worksheet_common->getHeaderFooter()->setEvenHeader($headerContent); //检测公司名称 + 报告编号前缀 + 报告编号(封底页眉头) Even
                $worksheet_common->getPageMargins()->setHeader($headerPageMargins); //设置眉头的页边距

                $worksheet_common->getHeaderFooter()->setOddFooter($footerContent); //页脚  Odd
                $worksheet_common->getHeaderFooter()->setEvenFooter($footerContent); //页脚  Even
                $worksheet_common->getPageMargins()->setFooter($footerPageMargins); //设置页脚的页边距

                $worksheet_common->getPageMargins()->setBottom($bottomPageMargins); //设置下边的页边距

                $spreadsheet->addExternalSheet($worksheet_common); //添加外部工作表
                //为了防止内存泄漏，建议用完手动清理
                $spreadsheet_common->disconnectWorksheets();
                unset($spreadsheet_common);
            }
        //================================================== 封底 end ============================================
        $sql = "SELECT DISTINCT
        A.std_id,
        A.tool_name,
        A.tool_id,
        A.unit
        FROM
        a_contract_gaiyao AS A
        WHERE
        A.contract_id = :contract_id";

        //查询出当前批次下 标准、工器具的集合，并去重
        $data_std_tool = DB::select($sql, ['contract_id' => $contract_id]);
        foreach ($data_std_tool as $k => $v) {
            // if ($v->tool_name == '安全帽') {
            //             continue;
            //         }
            # 查询数据的位置信息，即数据要存到Excel的哪个单元格
            $info_pos = DB::table('a_excel_pos as B')
            ->leftJoin('a_std_tool_rel as A', 'A.id', '=', 'B.std_tool_rel_id')
            ->where(['A.std_id' => $v->std_id, 'A.tool_id' => $v->tool_id, 'B.is_default' => 1])
            ->select('B.*', 'A.cycle', 'A.ctime', 'A.remark')->first();
            if (!$info_pos) {
                //为了防止内存泄漏，建议用完手动清理
                $spreadsheet->disconnectWorksheets();
                unset($spreadsheet);
                return "{$v->tool_name} 未指定数据在模板文件中的位置";
            }
            $posname = $info_pos->name === '默认' ? '' : $info_pos->name;
            # 检测模版文件是否存在
            $template_file = "./storage/ExcelTemplate/{$v->std_id}/{$v->tool_name}{$posname}.xls"; //考虑到兼容Linux，斜杠用“/”
            if (!file_exists($template_file)) {
                //为了防止内存泄漏，建议用完手动清理
                $spreadsheet->disconnectWorksheets();
                unset($spreadsheet);
                return "{$v->tool_name}的模板文件不存在";
            }

            //如果工器具solo，则一个页面出一个样品报告
            if($info_pos->solo_page == 1){
                $this->addSoloWorkSheet($spreadsheet,$template_file,$v,$contract_id);
                continue;
            }

            //未指定行号
            if ($info_pos->line_num === '' || intval($info_pos->line_num) < 5) {
                //为了防止内存泄漏，建议用完手动清理
                $spreadsheet->disconnectWorksheets();
                unset($spreadsheet);
                return "{$v->tool_name} 未指定数据在模板文件中的行号";
            }

            //==========================去具体工器具模版文件 复制表单 到 封面模板文件========================
            $spreadsheet2 = \PhpOffice\PhpSpreadsheet\IOFactory::load($template_file); //读取被复制的模板文件
            $worksheet3 = $spreadsheet2->getActiveSheet(); //在MS Excel（或其他适当的Spreadsheet程序）中打开工作簿时将处于活动状态的工作表

            $worksheet3->setTitle("HelloWorld-gww");
            $worksheet2 = clone $worksheet3; //按索引获取被复制的表单，从 0 开始
            $spreadsheet->addExternalSheet($worksheet2); //添加外部工作表
            //getSheetCount()方法将告诉您工作簿中的工作表数量,class Spreadsheet 的方法
            //为了防止内存泄漏，建议用完手动清理
            $spreadsheet2->disconnectWorksheets();
            unset($spreadsheet2);
            //=========================================================================================
            $worksheet = $spreadsheet->getSheetByName("HelloWorld-gww"); //按表单名 获取到激活的表单
            $worksheet->setTitle("{$v->tool_name}{$v->std_id}");
            // $worksheet->getPageSetup()->setPrintArea('A1:BA1000');//指定打印区域

            // if(!$worksheet) dd("{$v->tool_name}{$v->std_id}");//-------------------------------------------------------------------------------------------------------??????
            $worksheet->getHeaderFooter()->setOddHeader($headerContent); //检测公司名称 + 报告编号前缀 + 报告编号(检测报告正文页眉头)  Odd
            $worksheet->getHeaderFooter()->setEvenHeader($headerContent); //检测公司名称 + 报告编号前缀 + 报告编号(检测报告正文页眉头) Even
            $worksheet->getPageMargins()->setHeader($headerPageMargins); //设置眉头的页边距

            $worksheet->getHeaderFooter()->setOddFooter($footerContent); //页脚  Odd
            $worksheet->getHeaderFooter()->setEvenFooter($footerContent); //页脚  Even
            $worksheet->getPageMargins()->setFooter($footerPageMargins); //设置页脚的页边距

            $worksheet->getPageMargins()->setBottom($bottomPageMargins); //设置下边的页边距

            $DY_RiQi = strtotime($data_batch->created_at); //将表示时间和日期的字符串转化为相应的时间戳
            if ($info_pos->come_date !== '') {
                $comeDateCell = $info_pos->come_date;
                $worksheet->getCell($comeDateCell)->setValue(date("Y年m月d日", $DY_RiQi)); //到样日期
            }

            //------------------------------------------检测日期---------
            //检验日期开始时间
            $testdate_start = DB::table('a_sample_info as B')->leftJoin('a_contract_gaiyao as A', 'A.id', '=', 'B.contract_gaiyao_id')->where('B.contract_id', $contract_id)->where('std_id', $v->std_id)->where('tool_id', $v->tool_id)->min('test_time');
            if ($testdate_start) {
                $testdate_start = strtotime($testdate_start); //将任何字符串的日期时间描述解析为 Unix 时间戳
                $testdate_start = date("Y年m月d日", $testdate_start);
            }
            //检验日期结束时间
            $testdate_end = DB::table('a_sample_info as B')->leftJoin('a_contract_gaiyao as A', 'A.id', '=', 'B.contract_gaiyao_id')->where('B.contract_id', $contract_id)->where('std_id', $v->std_id)->where('tool_id', $v->tool_id)->max('test_time');
            if ($testdate_end) {
                $testdate_end = strtotime($testdate_end); //将任何字符串的日期时间描述解析为 Unix 时间戳
                $testdate_end = date("Y年m月d日", $testdate_end);
            }
            if ($testdate_start === $testdate_end) {
                # 开始日期与结束日期相同
                $test_date = $testdate_start;
            }
            else {
                # code...
                $test_date = "{$testdate_start}～{$testdate_end}";
            }
            if ($info_pos->test_date !== ''){
                $testDateCell = $info_pos->test_date;
	            $worksheet->getCell($testDateCell)->setValue($test_date); //检测日期
            }
            //-------------------------------------------------------------------
            $sample_count = DB::table('a_contract_gaiyao')->where('contract_id', $contract_id)->where('std_id', $v->std_id)->where('tool_id', $v->tool_id)->sum('count');
            if ($info_pos->sample_count !== '') {
                $sampleCountCell = $info_pos->sample_count;
                $worksheet->getCell($sampleCountCell)->setValue($sample_count . $v->unit); //样品数量
            }
            $temp_hum_pressure_data = DB::table('a_item_data as B')->select('temp', 'hum', 'pressure')->leftJoin('a_contract_gaiyao as A', 'A.id', '=', 'B.contract_gaiyao_id')->where('B.contract_id', $contract_id)->where('item_id', '!=', 61)->where('std_id', $v->std_id)->where('tool_id', $v->tool_id)->where('res', '<', 2)->get();
            // if ($k==1) dd($temp_hum_pressure_data);
            $se = self::tempHumPressureSE($temp_hum_pressure_data);
            [$temp, $hum, $pressure] = $se;
            //------------------------------------------ 温度 start---------
            if ($info_pos->temp !== '') {
                $worksheet->getCell($info_pos->temp)->setValue($temp); //温度
            }
            //------------------------------------------ 温度 end ---------
            //------------------------------------------ 相对湿度 start---------
            if ($info_pos->hum !== '') {
                $worksheet->getCell($info_pos->hum)->setValue($hum); //相对湿度
            }
            //------------------------------------------ 相对湿度 end ---------
            //------------------------------------------ 大气压力 start---------
            if ($info_pos->pressure !== '') {
                $worksheet->getCell($info_pos->pressure)->setValue($pressure); //大气压力
            }
            //------------------------------------------ 大气压力 end ---------
            //------------------------------------------ 天气 start---------
            $wea = DB::table('a_item_data as B')->leftJoin('a_contract_gaiyao as A', 'A.id', '=', 'B.contract_gaiyao_id')->where('B.contract_id', $contract_id)->where('std_id', $v->std_id)->where('tool_id', $v->tool_id)->where('wea', '!=', '')->value('wea');
            if ($info_pos->wea !== '') {
                if ($wea) {
                    $worksheet->getCell($info_pos->wea)->setValue($wea);
                } //天气
            }

            //------------------------------------------ 天气 end ---------

            $data_sample_info = DB::table('a_sample_info as B')->select('B.*', 'A.tool_name', 'A.voltage', 'A.class', 'A.std_id', 'A.tool_id', 'A.item_ids', 'A.vol_id', 'class_id')->leftJoin('a_contract_gaiyao as A', 'A.id', '=', 'B.contract_gaiyao_id')->where('B.contract_id', $contract_id)->where('std_id', $v->std_id)->where('tool_id', $v->tool_id)->get();
            $rowIndex = $initRowIndex = $info_pos->line_num;
            $sampleCount = count($data_sample_info);
            $hegeCount = $unhegeCount = $weiZhiCount = 0;
            $sampleMergeCells = $this->sampleMergeCells($worksheet,$info_pos); //样品内容合并单元格
            $sample_rowspann = $info_pos->sample_rowspann;  //单个样品占用行数

            foreach ($data_sample_info as $kk => $vv) {
                if ($kk > 0) {
                    # 键 >0 代表 不是第一次循环
                    //在第x行之前插入n个新行，插入单元格可能会引起样式错乱(待解决)
                    $worksheet->insertNewRowBefore((int)$rowIndex, (int)$sample_rowspann);
                    $this->toMergeCells($kk,$info_pos,$sampleMergeCells,$worksheet);
                    $this->sampleDefVal($worksheet,$info_pos,$kk,$sampleMergeCells);
                }
                if ($info_pos->serial_num1 !== '') {
                    $worksheet->getCell($info_pos->serial_num1 . $rowIndex)->setValue($kk + 1); //序号
                }
                if ($info_pos->sample_num1 !== '') {
                    // if ($vv->tool_name == '安全帽') {
                    //     dd($rowIndex,substr($vv->bar_code, 6) . '#',$info_pos,$vv);
                    // }
                    //$worksheet->getCell($info_pos->sample_num1 . $rowIndex)->setValue(substr($vv->bar_code, 6) . '#'); //样品号
                    $worksheet->getCell($info_pos->sample_num1 . $rowIndex)->setValue($vv->bar_code); //样品号
                }
                if ($info_pos->self_num1 !== '') {
                    $worksheet->getCell($info_pos->self_num1 . $rowIndex)->setValue($vv->self_num); //自编号
                }
                if ($info_pos->voltage1 !== '') {
                    $voltageWithoutKV = str_replace("kV", "", $vv->voltage); //将额定电压中的 kV去掉
                    $worksheet->getCell($info_pos->voltage1 . $rowIndex)->setValue($voltageWithoutKV); //额定电压
                }
                if ($info_pos->class1 !== '') {
                    if($vv->tool_name == '抱杆'){
                        $ca = explode('/',$vv->class);
                        $cl = isset($ca[1]) ? $ca[1] : '/';
                        $worksheet->getCell($info_pos->voltage1 . $rowIndex)->setValue($ca[0]); //额定电压
                        $worksheet->getCell($info_pos->class1 . $rowIndex)->setValue($cl); //分类
                    }else {
                        $worksheet->getCell($info_pos->class1 . $rowIndex)->setValue($vv->class); //分类
                    }
                    //$worksheet->getCell($info_pos->class1 . $rowIndex)->setValue($vv->class); //分类
                }
                if ($info_pos->res1 !== '') {
                    if ($vv->res === 1) {
                        $hegeCount += 1;
                        $panding = '合格';
                    }
                    elseif ($vv->res === 2) {
                        $unhegeCount += 1;
                        $panding = '不合格';
                    }
                    else {
                        $weiZhiCount += 1;
                        # code...
                        $panding = '—';
                    }
                    $worksheet->getCell($info_pos->res1 . $rowIndex)->setValue($panding); //判定
                }
                //有效日期有位置 而且 周期不为0或null
                if ($info_pos->valid_date1 !== '' && $info_pos->cycle) {
                    if ($vv->res === 1) {
                        //合格
                        $test_time = $vv->test_time; //试验日期
                        $cycle = ($info_pos->cycle) * 12; //周期

                        $valid_date = date("Y-m-d", strtotime("+$cycle month", strtotime($test_time)));
                        $first_date = date("Y-m-d", strtotime("first day of +$cycle month", strtotime($test_time)));
                        if (substr($valid_date, 0, 7) !== substr($first_date, 0, 7)) {
                            $valid_date = date("Y-m-d", strtotime("last day of +$cycle month", strtotime($test_time)));
                        }

                        $valid_date = date('Y-m-d', strtotime("-1 day", strtotime($valid_date)));
                    }
                    else {
                        # 不合格
                        $valid_date = "—";
                    }
                    $worksheet->getCell($info_pos->valid_date1 . $rowIndex)->setValue($valid_date); //有效日期
                }
                //------------------------------------------ 试验条件 start ---------
                $key_condition = DB::table('a_condition_key')->select('id', 'item_id', 'pos_start', 'pos_end')->where([['std_id', '=', $vv->std_id], ['tool_id', '=', $vv->tool_id], ['pos_start', '!=', ''], ['pos_end', '!=', '']])->get();
                foreach ($key_condition as $vvv) {
                    $arr_item_id = explode(',', $vv->item_ids); //字符串 --》数组
                    $b = in_array($vvv->item_id, $arr_item_id); //in_array($value, $array);
                    if ($b) {
                        # code...
                        $val_condition = DB::table('a_condition')->where('condition_key_id', '=', $vvv->id)->whereIn('vol_class_id', [0, $vv->vol_id, $vv->class_id])->value('val');
                    }
                    else {
                        # code...
                        $val_condition = '/';
                    }
                    $vvvstart = $vvv->pos_start;
                    $vvvend = $vvv->pos_end;

                    if ($vvvstart && $vvvend) {
                        $worksheet->mergeCells($vvvstart . $rowIndex . ':' . $vvvend . $rowIndex); //试验条件  合并单元格
                        $worksheet->getCell($vvvstart . $rowIndex)->setValue($val_condition); //试验条件
                    }
                } //内内层 foreach 结尾

                //------------------------------------------ 试验条件 end ---------

                //------------------------------------------ 单项目的检测结果（符合、不符合） start ---------
                $item_res = DB::table('a_std_tool_item_rel')->select('id', 'testitem_id','hege_jielun', 'pos_start', 'pos_end')->where([['std_id', '=', $vv->std_id], ['tool_id', '=', $vv->tool_id]])->get();
                $str_max_array = [];
                foreach ($item_res as $kkk=>$vvv) {
                    $arr_item_id = explode(',', $vv->item_ids); //字符串 --》数组
                    $b = in_array($vvv->testitem_id, $arr_item_id); //in_array($value, $array);
                    if ($b) {
                        # code...
                        $test_item = DB::table('a_item_data')->where([
                            ['sample_info_id', '=', $vv->id],
                            ['item_id', '=', $vvv->testitem_id]
                        ])->select('remark','res')->first();
                        $test_res = $test_item->res;
                        $remark = $test_item->remark;   //不符合时的备注信息
                        //结论,0-不符合，1-符合，2-未录入
                        if ($test_res === 0) {
                            $test_res = $remark != '' || $remark != '无' ? $remark : '不符合';
                            $str_max_array[$kkk] = mb_strlen($test_res);
                        }
                        elseif ($test_res === 1) {
                            //外观只显示符合不符合，其他项目显示在工器具-关联项目中录入的“检测结果（合格）”信息
                            $test_res = $vvv->testitem_id == 61 ? '检测结果符合标准要求' : (!empty($vvv->hege_jielun)?$vvv->hege_jielun:'符合');
                            $str_max_array[$kkk] = mb_strlen($test_res);
                        }
                        elseif ($test_res === 3) {
                            $test_res = '/';
                        }
                        else {
                            # code...
                            $test_res = '/';
                        }
                    }
                    else {
                        # code...
                        $test_res = '/';
                    }
                    $vvvstart = $vvv->pos_start;
                    $vvvend = $vvv->pos_end;

                    if ($vvvstart && $vvvend) {
                        //$worksheet->mergeCells($vvvstart . $rowIndex . ':' . $vvvend . $rowIndex); //单项目的检测结果  合并单元格
                        $worksheet->getCell($vvvstart . $rowIndex)->setValue($test_res); //单项目的检测结果
                    }
                    //------------------------------------------ 试验数据 start ---------
                    $key_data = DB::table('a_testdata_key')
                            ->select('id', 'item_id', 'pos_start', 'pos_end', 'std_tool_item_rel_id','line_num')
                            ->where('std_tool_item_rel_id',$vvv->id)
                            ->where([['pos_start', '!=', ''], ['pos_end', '!=', '']])
                            ->get();
                    foreach ($key_data as $kdKey => $vvv2) {
                        $arr_item_id = explode(',', $vv->item_ids); //字符串 --》数组
                        $b = in_array($vvv2->item_id, $arr_item_id); //in_array($value, $array);

                        if ($b) {
                            $val_testdata = DB::table('a_input_data')->where([['sample_info_id', '=', $vv->id], ['testdata_key_id', '=', $vvv2->id]])->value('val');
                            //某些工器具(安全帽，辅助型绝缘靴)还要加上检测结果文字描述
                            if($val_testdata && in_array($vv->tool_id,[1,18])){
                                $val_testdata = $val_testdata.'；'.$vvv->hege_jielun;
                            }
                            // dd( "{$val_testdata} --- {$vv->id}---- {$vvv2->id}");
                        } else {
                            # code...
                            $val_testdata = '/';
                        }

                        try {
                            $vvv2start = $vvv2->pos_start;
                            $vvv2end = $vvv2->pos_end;
                            $vvv2LineNum = $vvv2->line_num;
                        }catch(\Exception $exception){
                            $vvv2start = $vvv2end = $vvv2LineNum = '';
                        }
                        if ($vvv2start && $vvv2end) {
                            $rowIndexItemData = $vvv2LineNum ? $rowIndex+($vvv2LineNum-$initRowIndex) : $rowIndex;
                            $worksheet->getCell($vvv2start . $rowIndexItemData)->setValue(' '.$val_testdata.' '); //试验数据
                        }
                    } //内内层 foreach 结尾
                    //------------------------------------------ 试验数据 end ---------
                } //内内层 foreach 结尾
                //------------------------------------------ 单项目的检测结果（符合、不符合） end   ---------
                $heigth_base = $str_max_array ? max($str_max_array) : 10;   //默认10个汉字
                $height = round($heigth_base * (90/21));
                $height = $height < 48 ? 36:$height;
                $worksheet->getRowDimension($rowIndex)->setRowHeight($height);  //动态设置行高
                $rowIndex += $sample_rowspann;

            } //内层 foreach 结尾

            //检测结论，检验结论
            $jlcellstr = trim($worksheet->getCell('A' . $rowIndex)->getValue());
            if ($jlcellstr === '检测结论' || $jlcellstr === '检验结论') {
                $range = $worksheet->getCell('A' . $rowIndex)->getMergeRange();
                $rangeAry = explode(':', $range);
                $ABCpos = dec2ABC(ABC2decimal(substr($rangeAry[1], 0, 1)) + 1);
                $worksheet->getCell($ABCpos . $rowIndex)->setValue("总数：$sampleCount 件,其中合格：$hegeCount 件，不合格：$unhegeCount 件,未知结论：$weiZhiCount 件");
            }

        } //最外层 foreach 结尾

        //================================================== 样品信息 start ============================================
        $template_file = './storage/ExcelTemplate/样品信息.xls'; //考虑到兼容Linux，斜杠用“/”
        $exist_sample_info_excel = file_exists($template_file);
        if ($exist_sample_info_excel) {
            $spreadsheet_sam_info = \PhpOffice\PhpSpreadsheet\IOFactory::load($template_file); //读取模板文件
            $worksheet_sam_info = $spreadsheet_sam_info->getSheet(0); //按索引获取表单，从 0 开始
            $worksheet_sam_info->setTitle('样品信息'); //重命名工作表
            $spreadsheet->addExternalSheet($worksheet_sam_info); //添加外部工作表
            //为了防止内存泄漏，建议用完手动清理
            $spreadsheet_sam_info->disconnectWorksheets();
            unset($spreadsheet_sam_info);

            $worksheet = $spreadsheet->getSheetByName('样品信息'); //按表单名 获取到激活的表单

            $worksheet->getHeaderFooter()->setOddHeader($headerContent); //检测公司名称 + 报告编号前缀 + 报告编号(样品信息页眉头)  Odd
            $worksheet->getHeaderFooter()->setEvenHeader($headerContent); //检测公司名称 + 报告编号前缀 + 报告编号(样品信息页眉头) Even
            $worksheet->getPageMargins()->setHeader($headerPageMargins); //设置眉头的页边距

            $worksheet->getHeaderFooter()->setOddFooter($footerContent); //页脚  Odd
            $worksheet->getHeaderFooter()->setEvenFooter($footerContent); //页脚  Even
            $worksheet->getPageMargins()->setFooter($footerPageMargins); //设置页脚的页边距

            $worksheet->getPageMargins()->setBottom($bottomPageMargins); //设置下边的页边距

            $data_sample_info = DB::table('a_sample_info as B')->select('B.contract_gaiyao_id','B.bar_code','B.sample_status','B.res','A.tool_name', 'A.voltage', 'A.class')->leftJoin('a_contract_gaiyao as A', 'A.id', '=', 'B.contract_gaiyao_id')->where('B.contract_id', $contract_id)->orderBy('bar_code')->get();

            //重组样品信息为：$yangpin[批次概要id][是否合格][样品信息]
            $yangpin = [];
            foreach($data_sample_info as $kkey=>$vval){
                $yangpin[$vval->contract_gaiyao_id][$vval->res][] = $vval;
            }
            $rowIndex_sam_info = 3; //样品信息的行号
            $kkrow = 0;
            foreach($yangpin as $toolName=>$voltage){                       //循环外层批次概要id数组
                foreach($voltage as $volName=>$class){                      //循环二层是否合格数组
                    if ($kkrow > 0) {
                        $worksheet->insertNewRowBefore($rowIndex_sam_info, 1); //在第x行之前插入1个新行
                    }
                    $yangpinBarCode = [];
                    $ypSampleStatus = '';
                    foreach($class as $kkeeyy=>$value){       //循环终极解决样品编号与获取最后一个样品的样品状态
                        $yangpinBarCode[$kkeeyy] = $value->bar_code;
                        $ypSampleStatus = $value->sample_status;
                    }
                    $yangpinBC = '';
                    $sample_res = '/';
                    if($volName == 1) {     //合格显示样品编码范围
                        $min = $yangpinBarCode ? min($yangpinBarCode) : '';
                        $max = $yangpinBarCode ? max($yangpinBarCode) : '';
                        $yangpinBC = ($min!==''&&$max!==''&&$min!=$max)?"{$min}～{$max}":($max?"{$max}":'');
                        $sample_res = '合格';
                    }else if($volName == 2){ //不合格的显示具体样品编码
                        $yangpinBC = implode('、',$yangpinBarCode);
                        $sample_res = '不合格';
                    }

                    $worksheet->setPrintGridlines(true);
                    $worksheet_sam_info->getCell("A" . $rowIndex_sam_info)->setValue($class[0]->tool_name); //名称
                    $worksheet_sam_info->getCell("B" . $rowIndex_sam_info)->setValue($yangpinBC); //样品编号
                    $worksheet_sam_info->getCell("C" . $rowIndex_sam_info)->setValue(count($class)); //样品数量
                    $worksheet_sam_info->getCell("D" . $rowIndex_sam_info)->setValue($class[0]->class); //规格型号
                    $worksheet_sam_info->getCell("E" . $rowIndex_sam_info)->setValue($class[0]->voltage); //额定电压
                    $worksheet_sam_info->getCell("F" . $rowIndex_sam_info)->setValue($sample_res); //是否合格
                    $worksheet_sam_info->getCell("G" . $rowIndex_sam_info)->setValue($ypSampleStatus); //样品状态
                    $kkrow++;
                    $rowIndex_sam_info++;
                }
            }

            /*$rowIndex_sam_info = 3; //样品信息的行号
            foreach ($data_sample_info as $kk => $vv) {
                if ($kk > 0) {
                    $worksheet->insertNewRowBefore($rowIndex_sam_info, 1); //在第x行之前插入1个新行
                }
                $worksheet->setPrintGridlines(true);
                $worksheet_sam_info->getCell("A" . $rowIndex_sam_info)->setValue($vv->tool_name); //样品信息---样品名称
                //$worksheet_sam_info->getCell("B" . $rowIndex_sam_info)->setValue(substr($vv->bar_code, 6) . '#'); //样品号
                $worksheet_sam_info->getCell("B" . $rowIndex_sam_info)->setValue($vv->bar_code); //样品信息---样品编号
                $worksheet_sam_info->getCell("C" . $rowIndex_sam_info)->setValue(1); //样品信息---样品自编号
                //$worksheet_sam_info->getCell("D" . $rowIndex_sam_info)->setValue($vv->self_num); //样品信息---样品自编号
                $worksheet_sam_info->getCell("D" . $rowIndex_sam_info)->setValue($vv->class); //样品信息---规格型号
                $worksheet_sam_info->getCell("E" . $rowIndex_sam_info)->setValue($vv->voltage); //样品信息---额定电压
                $worksheet_sam_info->getCell("F" . $rowIndex_sam_info)->setValue($vv->sample_status); //样品信息---样品状态
                //$worksheet_sam_info->getCell("H" . $rowIndex_sam_info)->setValue($vv->brand); //样品信息---商标
                //$worksheet_sam_info->getCell("I" . $rowIndex_sam_info)->setValue($vv->manufacturer); //样品信息---生产厂家
                //$worksheet_sam_info->getCell("J" . $rowIndex_sam_info)->setValue($vv->production_date); //样品信息---生产日期

                $rowIndex_sam_info++;
            }*/
        }
        //================================================== 样品信息 end ============================================

        // $worksheet = $spreadsheet->getActiveSheet();//获取到激活的表单
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls');
        /* 生成HTML */ // $writer = new \PhpOffice\PhpSpreadsheet\Writer\Html($spreadsheet);
        // $writer->writeAllSheets();
        // $writer->save("05featuredemo.htm");
        /* 生成HTML */

        //将xls文件保存,将Excel文件保存在服务器的磁盘上
        try{
            $disk->put($storeFile,'');
            $writer->save($disk->path($storeFile));
        }catch(Exception $e){
            $disk->delete($storeFile);
        }

        //----------直接在浏览器下载----start
        $file_name = "检测报告" . $data_batch->batchNum . ".xls"; //输出的文件名称
        // header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//告诉浏览器输出Excel 07文件
        header('Content-Type: application/vnd.ms-excel'); //告诉浏览器输出Excel 03版本文件
        header('Content-Disposition: attachment;filename=' . urlencode($file_name)); //告诉浏览器输出的文件名称
        header('Cache-Control: max-age=0'); //禁止缓存
        //----------直接在浏览器下载----end

        // $writer->save('write.xls');//将Excel文件保存在服务器的磁盘上
        $writer->setPreCalculateFormulas(false); //禁用公式预先计算
        $writer->save('php://output'); //直接在浏览器下载

         //未测试通过
        // $writer = new \PhpOffice\PhpSpreadsheet\Writer\Pdf\Mpdf($spreadsheet);
        // $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Mpdf');
        // $writer->setPreCalculateFormulas(false);//禁用公式预先计算
        // $writer->save("05featuredemo.pdf"); 

        //为了防止内存泄露，建议用完手动清理
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        
    }

    //温湿度，大气压最大最小值
    public static function tempHumPressureSE($data) {
        if (is_object($data)) {
            $data = $data->toArray();
        }
        // dd($data);
        $temps = ary_filter(array_column($data,'temp'));
        $min = $temps ? min($temps) : '';
        $max = $temps ? max($temps) : '';
        $temp = ($min !== '' && $max !== '' && $min != $max)  ? "({$min}～{$max})℃" : ($max ? "{$max}℃" : '');
        $hums = ary_filter(array_column($data,'hum'));
        $min = $hums ? min($hums) : '';
        $max = $hums ? max($hums) : '';
        $hum = ($min !== '' && $max !== '' && $min != $max) ? "({$min}～{$max})%RH" : ($max ? "{$max}%RH" : '');
        $pressures = ary_filter(array_column($data,'pressure'));
        $min = $pressures ? min($pressures) :'';
        $max = $pressures ? max($pressures) : '';
        $pressure = ($min !== '' && $max !== '' && $min != $max) ? "({$min}～{$max})kPa" : ($max ? "{$max}kPa" : '');
        return [$temp, $hum, $pressure];
    }

    //获取第一个样品的表格信息（合并信息及数据）
    public function sampleMergeCells($worksheet,$info_pos){
        $mergeCells = $worksheet->getMergeCells();
        $initRowIndex = $info_pos->line_num;
        $intRowIndexMax = $initRowIndex + $info_pos->sample_rowspann - 1;
        $toMerge = [];
        foreach($mergeCells as $cells){
            $cell = explode(':',$cells)[0];
            $len = strlen($cell);
            for($i = 0; $i < $len; $i++) {
                if(!preg_match('/[A-Za-z]/',substr($cell,$i,1))){
                    $int = substr($cell,$i);
                    if($int >= $initRowIndex && $int <= $intRowIndexMax){
                        $toMerge[] = $cells;
                    }
                    continue;
                }
            }
        }
        return $toMerge;
    }

    /*合并新增加的相关行，并对有数据的行添加内容*/
    public function toMergeCells($kk,$info_pos,$sampleMergeCells,$worksheet){
        $sample_rowspann = $info_pos->sample_rowspann;
        foreach($sampleMergeCells as $cells){
            $cellsAry = explode(':',$cells);
            $startABCint = $this->splitABCint($cellsAry[0]);
            $endABCint = $this->splitABCint($cellsAry[1]);
            $toMergeCells[] = [$startABCint,$endABCint];
            $start = $startABCint[0] . ($startABCint[1] + $kk*$sample_rowspann);
            $end = $endABCint[0] . ($endABCint[1] + $kk*$sample_rowspann);
            $toMerge = "{$start}:{$end}";
            //合并
            $worksheet->mergeCells($toMerge);
        }
    }

    private function splitABCint($str){
        $len = strlen($str);
        for($j = 0; $j < $len; $j++) {
            if(!preg_match('/[A-Za-z]/',substr($str,$j,1))){
                $int = substr($str,$j-1);
                $abc = substr($str,0,$j-1);
            }
        }
        return [$abc,$int];
    }

    public function sampleDefVal($worksheet,$info_pos,$kk,$sampleMergeCells){
        $sample_rowspann = $info_pos->sample_rowspann;
        $initRowIndex = (int)$info_pos->line_num;
        $Amerge = $worksheet->getCell('A1')->getMergeRange();
        $maxMerge = explode(':',$Amerge)[1];
        $maxColumn = $worksheet->getCell($maxMerge)->getColumn();
        $maxRowIndex = $initRowIndex + $sample_rowspann - 1;
        $mergeCells = $sampleMergeCells;

        for($i = 1; $i <= ABC2decimal($maxColumn); $i ++) {
            for($j = $initRowIndex; $j <= $maxRowIndex; $j ++) {
                $col = dec2ABC($i);
                $cell = $col.$j;
                $range = $worksheet->getCell($cell)->getMergeRange();
                if(in_array($range,$mergeCells)){
                    continue;
                }
                if($range){
                    $mergeCells[] = $range;
                }
            }
        }
        foreach($mergeCells as $cells){
            $cell = explode(':',$cells)[0];
            if($def = $worksheet->getCell($cell)->getValue()) {
                $col = $worksheet->getCell($cell)->getColumn();
                $row = substr($cell,strlen($col));
                $row2 = $row + $kk*$sample_rowspann;
                $cell = $col.$row2;
                $worksheet->getCell($cell)->setValue($def);
                $height = $worksheet->getRowDimension($row)->getRowHeight();
                $worksheet->getRowDimension($row2)->setRowHeight($height);
            }
        }
    }

    private function addSoloWorkSheet($spreadsheet,$template_file,$v,$contract_id){
        $data_sample_info = DB::table('a_sample_info as A')
        ->leftJoin('a_contract_gaiyao as B', 'B.id', '=', 'A.contract_gaiyao_id')
        ->leftJoin('a_energy_std as C', 'C.id', '=', 'A.energy_std_id')
        ->where('B.contract_id', $contract_id)
        ->where('B.std_id', $v->std_id)
        ->where('B.tool_id', $v->tool_id)
        ->select('A.*', 'B.tool_name', 'B.voltage', 'B.class', 'B.std_id', 'B.tool_id', 'B.item_ids', 'B.vol_id', 'B.class_id','C.impedance','C.no_load_loss','load_loss_dy11','C.load_loss_yz11','C.load_loss_yy0')
        ->get();
        $info_pos = DB::table('a_excel_pos as B')
        ->leftJoin('a_std_tool_rel as A', 'A.id', '=', 'B.std_tool_rel_id')
        ->where(['A.std_id' => $v->std_id, 'A.tool_id' => $v->tool_id, 'B.is_default' => 1])
        ->select('B.*', 'A.cycle', 'A.ctime', 'A.remark')->first();

        //铭牌信息-位置
        $plates = DB::table('a_tool_plate_excel_pos')->where('excel_pos_id',$info_pos->id)->pluck('pos','plate_name')->toArray();
        foreach($data_sample_info as $k=> $sampleItem){
            $title = "{$v->tool_name}{$v->std_id}{$k}";
            //==========================去具体工器具模版文件 复制表单 到 封面模板文件========================
            $spreadsheet2 = \PhpOffice\PhpSpreadsheet\IOFactory::load($template_file); //读取被复制的模板文件
            $worksheet3 = $spreadsheet2->getActiveSheet(); //在MS 
            $worksheet3->setTitle($title);
            $worksheet2 = clone $worksheet3;
            $spreadsheet->addExternalSheet($worksheet2); //添加外部工作表
            $spreadsheet2->disconnectWorksheets();
            unset($spreadsheet2);
            $worksheet = $spreadsheet->getSheetByName($title); //按表单名 获取到激活的表单
            $worksheet->setTitle("{$v->tool_name}".substr($sampleItem->bar_code,-3));
            //=========================================================================================

            //+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++基本信息+++++++++++++++++++
            if($info_pos->sample_num1){
                $worksheet->getCell($info_pos->sample_num1)->setValue($sampleItem->bar_code);
            }
            //+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++项目结果填充+++++++++++++++++++
            $item_data = DB::table('a_std_tool_item_rel as a')
                ->leftJoin('a_item_data as b','b.item_id','=','a.testitem_id')
                ->leftJoin('manager as c','c.id','=','b.manager_id')
                ->where('a.tool_id',$sampleItem->tool_id)
                ->where('a.std_id',$sampleItem->std_id)
                ->where('b.sample_info_id',$sampleItem->id)
                ->select('a.pos_start','a.pos_end','a.pos_date','a.pos_tester','b.item_id','b.utime','b.res','b.station_ids','c.realname','c.partner')
                ->get();

            // dd($item_data);

            $station_ids = "";
            foreach($item_data as $item){
                // 结论,0-不符合，1-符合，2-未录入,3-未试验
                $station_ids .= ",".$item->station_ids;
                $res = $item->res;
                $worksheet->getCell($item->pos_start)->setValue($res == 0 ? '不合格':($res ==  1? '合格':  " "));    
                $worksheet->getCell($item->pos_date)->setValue(substr($item->utime,0,10));    
                $worksheet->getCell($item->pos_tester)->setValue($item->realname.($item->partner ?'，'.$item->partner : " "));    
            }

            //+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++项目数据填充+++++++++++++++++++
            $input_data = DB::table('a_input_data as a')
            ->leftJoin('a_testdata_key as b','a.testdata_key_id','=','b.id')
            ->where('a.sample_info_id',$sampleItem->id)
            ->select('a.val','b.pos_start','b.pos_end','b.line_num')
            ->get();

            foreach($input_data as $item){
                if($item->pos_start && $item->line_num){
                    $worksheet->getCell($item->pos_start.$item->line_num)->setValue(is_null($item->val)?'/':$item->val);   
                } 
            }
            
            //+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++铭牌填充+++++++++++++++++++
            $gqj_plate = DB::table('a_gqj_plate')->where('bar_code_1st',$sampleItem->bar_code_1st)->value('plate');

            $gqj_plate = json_decode($gqj_plate);
            if(!$gqj_plate){
                return '样品号：'.$sampleItem->bar_code.'，信息未录入！';
            }
            foreach($gqj_plate as $item){
                if(isset($plates[$item->name]) && $plates[$item->name]){
                    $worksheet->getCell($plates[$item->name])->setValue($item->val);
                }
            }

            //规定值
            if($sampleItem->energy_std_id){
                //获取连接组标号
                $connet = array_map(function ($item)
                {
                    if($item->name == '连接组标号'){
                        return $item->val;
                    }
                },$gqj_plate);
                $connet = array_values(array_filter($connet))[0];
                // dd($connet,$gqj_plate);
                $connet = strtolower(str_replace('n', '', $connet));
                if(in_array($connet, ['dy11','yy0','yz11'])){
                    $energy_std_pos = DB::table('a_energy_std_excel_pos')->where('excel_pos_id',$info_pos->id)->first();
                    //空载
                    $worksheet->getCell($energy_std_pos->no_load_loss)->setValue(($sampleItem->no_load_loss));
                    //负载
                    switch($connet){
                        case 'dy11':
                            $fuzai = $sampleItem->load_loss_dy11;
                        break;
                        case 'yz11':
                            $fuzai = $sampleItem->load_loss_yz11;
                        break;
                        case 'yy0':
                            $fuzai = $sampleItem->load_loss_yy0;    
                        break;
                            $fuzai = '';
                        default:;
                    }
                    if($fuzai){
                        $worksheet->getCell($energy_std_pos->load_loss)->setValue($fuzai);
                    }
                    //短路阻抗
                    $worksheet->getCell($energy_std_pos->impedance)->setValue($sampleItem->impedance);
                }
            }
            
            //试验设备填充
            $stationPos = DB::table('a_station_excel_pos')->where('excel_pos_id',$info_pos->id)->first();
            $rowIndex = $stationPos->line_num;
            $stationPos = array_filter((array)$stationPos);
            unset($stationPos['id'],$stationPos['excel_pos_id'],$stationPos['ctime'],$stationPos['line_num']);
            $stationIdAry = array_filter(explode(',',$station_ids));
            $stations = DB::table('a_station')->whereIn('id',$stationIdAry)->get();
            foreach ($stations as $k => $station) {
                if ($k > 0) {
                    # 键>0 代表不是第一次循环
                    $worksheet->insertNewRowBefore($rowIndex, 1); //在第x行之前插入1个新行
                    foreach($stationPos as $item){
                        $itemPos = explode('-',$item);
                        $worksheet->mergeCells($itemPos[0].$rowIndex.':'.$itemPos[1].$rowIndex);
                    }
                }
                $worksheet->getCell('A'.$rowIndex)->setValue($k+1);
                foreach($stationPos as $coluName=> $item){
                    $itemPos = explode('-',$item);
                    if($coluName != 'serial_num'){
                        $worksheet->getCell($itemPos[0].$rowIndex)->setValue($station->$coluName);
                    }
                }
                $worksheet->getRowDimension($rowIndex)->setRowHeight(25);
                $rowIndex += 1;
            }
        }
    }

}
